/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package com.elcom.emocbc.cbm.smsbroadcast;

import com.elcom.emocbc.cbm.BroadcastDTO;
import com.elcom.emocbc.dbmapping.persistence.DbAccess;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import org.apache.commons.dbutils.DbUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 *
 * @author GiangLT
 */
public class SmsBroadcastDao {

    private final Logger logger = LoggerFactory.getLogger(SmsBroadcastDao.class);
    public static String SQL_GET_LIST_BROADCAST = "SELECT bt.broadcasttime_id "
            + "	,bt.zone_id "
            + "	,bt.createdby "
            + "	,bt.createddate "
            + "	,p.program_id "
            + "	,cpt.topic_id "
            + "	,z.zone_name "
            + "	,bt.starttime "
            + "	,bt.program_indexs "
            + "	,bt.cp_topic_id "
            + "	,p.subcontents "
            + "	,cp.cpname "
            + "	,t.topicname "
            + " ,cp.cp_id "
            + " ,p.chain_id "
            + "FROM broadcasttime bt "
            + "	,cp_topic cpt "
            + "	,zone z "
            + "	,programs p "
            + "	,cp cp "
            + "	,topic t "
            + "WHERE bt.zone_id = z.zone_id "
            + "	AND bt.STATUS != 5 "
            + "	AND p.STATUS = 1 "
//            + "	AND trunc(p.timeplug) = trunc(sysdate) "
            + "	AND bt.cp_topic_id = cpt.cp_topic_id "
            + "	AND ( "
            + "		bt.cp_topic_id = p.cps_topic_id "
            + "		AND bt.program_indexs = p.indexs "
            + "		) "
            + "	AND cpt.cp_id = cp.cp_id "
            + "	AND t.topic_id = cpt.topic_id "
            + "	AND bt.starttime = ?"
            + "	AND z.zone_id = ?";

    public BroadcastDTO getBroadcast(String startTime, int zoneId) {
        logger.debug("Test broadcast from DB");
        BroadcastDTO broadcast = null;
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        logger.debug("Get broadcast from DB");

        try {
            conn = DbAccess.getInstance().getConnection();
            stmt = conn.prepareStatement(SQL_GET_LIST_BROADCAST);
//			stmt.setQueryTimeout(3);
            stmt.setString(1, startTime);
            stmt.setInt(2, zoneId);
            logger.debug("Querying broadcast with starttime {} and zoneId");
            rs = stmt.executeQuery();
            if (rs.next()) {
                logger.debug("Got 1 broadcast");
                broadcast = new BroadcastDTO();
                broadcast.setTopicID(rs.getInt("topic_id"));
                broadcast.setProgramID(rs.getString("program_id"));
                broadcast.setZone(rs.getString("zone_name"));

                DateFormat f_short = new SimpleDateFormat("dd/MM/yyyy");
                DateFormat f_full = new SimpleDateFormat("dd/MM/yyyy HH:mm");
                String start = f_short.format(new Date(System.currentTimeMillis())) + " " + rs.getString("starttime");
                Date startDate = f_full.parse(start);
                broadcast.setStartDate(startDate);
                broadcast.setStartTime(rs.getString("starttime"));
                broadcast.setEndDate(new Date(startDate.getTime() + 60000l));
                broadcast.setCpTopicId(rs.getInt("cp_topic_id"));
                broadcast.setIndex(rs.getInt("program_indexs"));

                broadcast.setBroadcastID(rs.getInt("broadcasttime_id"));
                broadcast.setZoneID(rs.getLong("zone_id"));
                broadcast.setCreatedBy(rs.getString("createdby"));
                broadcast.setCreatedDate(rs.getDate("createddate"));
                broadcast.setCpName(rs.getString("cpname"));
                broadcast.setSubContent(rs.getString("subcontents"));
                broadcast.setTopicName(rs.getString("topicname"));
                broadcast.setCpId(rs.getInt("cp_id"));
                broadcast.setChainId(rs.getString("chain_id"));
            }
            logger.debug("Finish Get broadcast from DB");
        } catch (Exception e) {
            logger.error("Exception when get broadcast from DB: ", e);
        } finally {
            DbUtils.closeQuietly(conn, stmt, rs);
        }

        return broadcast;
    }
}
